package com.swn.common.util;


/**
 * Created by SUNYM on 2014/8/26.
 */

import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONObject;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 导出Excel工具类
 * @author taozhaocai
 *
 */
public class ExcelUtils {

    private static String sheetName = "sheet";//工作表名
    private static List<String> columnNameList = new ArrayList<String>(0);//列名
    
    private static List<JSONObject> contentList = new ArrayList<JSONObject>(0);//单元格内容，逗号分隔
    private static List<Object[]> contentListObject = new ArrayList<Object[]>(0);//单元格内容，数组形式集合，根据项目需要确定使用哪一种内容形式

    private static String dateFormat = "yyyy-MM-dd";//日期格式
    private static String dataFormat = "#,##00.00";//数值格式
    private static int columnWidth = 8000;

    private static int pageNumber = 50000;//一个工作表中记录条数 ，如果如生成一工作表，则把总记录条数赋值此变量

    public static String getSheetName() {
        return sheetName;
    }

    public static List<String> getColumnNameList() {
        return columnNameList;
    }

    public static List<JSONObject> getContentList() {
        return contentList;
    }

    public static String getDateFormat() {
        return dateFormat;
    }

    public static String getDataFormat() {
        return dataFormat;
    }

    public static int getPageNumber() {
        return pageNumber;
    }

    public static int getColumnWidth() {
        return columnWidth;
    }

    public static void setSheetName(String sheetName) {
        ExcelUtils.sheetName = sheetName;
    }

    public static void setColumnNameList(List<String> columnNameList) {
        ExcelUtils.columnNameList = columnNameList;
    }

    public static void setContentList(List<JSONObject> contentList) {
        ExcelUtils.contentList = contentList;
    }

    public static void setDateFormat(String dateFormat) {
        ExcelUtils.dateFormat = dateFormat;
    }

    public static void setDataFormat(String dataFormat) {
        ExcelUtils.dataFormat = dataFormat;
    }

    public static void setColumnWidth(int columnWidth) {
        ExcelUtils.columnWidth = columnWidth;
    }

    public static void setPageNumber(int pageNumber) {
        ExcelUtils.pageNumber = pageNumber;
    }

    public static List<Object[]> getContentListObject() {
		return contentListObject;
	}

	public static void setContentListObject(List<Object[]> contentListObject) {
		ExcelUtils.contentListObject = contentListObject;
	}

	//日期样式
    public static CellStyle getCellStyleDate(Workbook wb){
        CreationHelper creationHelper = wb.getCreationHelper();
        CellStyle cellStyleDate = wb.createCellStyle();
        cellStyleDate.setDataFormat(creationHelper.createDataFormat().getFormat(dateFormat));
        return cellStyleDate;
    }

    //数字样式
    public static CellStyle getCellStyleData(Workbook wb)
    {
        CellStyle cellStyleData = wb.createCellStyle();
        DataFormat format = wb.createDataFormat();
        cellStyleData.setDataFormat(format.getFormat(dataFormat));
        return cellStyleData;
    }

    //强制换行
    public static CellStyle  getCellStyleNewLine(Workbook wb)
    {
        CellStyle cellStyle=  wb.createCellStyle();
        cellStyle.setWrapText(true);
        return  cellStyle;
    }
    
    //对齐方式
    public static CellStyle  getCellStyleAlignCenter(Workbook wb){
        CellStyle cellStyle=  wb.createCellStyle();
     // 设置单元格内容水平对其方式   
        // XSSFCellStyle.ALIGN_CENTER       居中对齐   
        // XSSFCellStyle.ALIGN_LEFT         左对齐   
        // XSSFCellStyle.ALIGN_RIGHT        右对齐   
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);   
           
        // 设置单元格内容垂直对其方式   
        // XSSFCellStyle.VERTICAL_TOP       上对齐   
        // XSSFCellStyle.VERTICAL_CENTER    中对齐   
        // XSSFCellStyle.VERTICAL_BOTTOM    下对齐   
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);   
        return cellStyle;
    }
    //创建EXCEL文件
    public static void writeExcel(OutputStream outputStream) throws IOException {
//      定义输出流
    	Workbook wb = new XSSFWorkbook();
//      生成文件内容
        int sheetCount = contentList.size() % pageNumber == 0 ? contentList.size()/pageNumber : contentList.size()/pageNumber +1;
        for(int i = 1 ; i<= sheetCount; i++)
        {
            Sheet sheet = createSheet(wb , sheetName+i);
            createRow(sheet, wb , i);
        }
        wb.write(outputStream);
    }
    
  //创建EXCEL文件
    public static void writeExcelObject(OutputStream outputStream) throws IOException {
//      定义输出流
    	Workbook wb = new XSSFWorkbook();
//      生成文件内容
        int sheetCount = contentListObject.size() % pageNumber == 0 ? contentListObject.size()/pageNumber : contentListObject.size()/pageNumber +1;
        for(int i = 1 ; i<= sheetCount; i++)
        {
            Sheet sheet = createSheet(wb , sheetName+i);
            createRowObject(sheet, wb , i);
        }
        wb.write(outputStream);
    }

    //  创建SHEET
    public static Sheet createSheet(Workbook wb ,String sheetName)
    {
        Sheet sheet = wb.createSheet(sheetName);
        return sheet;
    }

    //创建ROW 和CELL
    @SuppressWarnings("unused")
	public static void createRow(Sheet sheet , Workbook wb ,int page)
    {
        CellStyle cellStyleNewLine = getCellStyleNewLine(wb);
        int cellCount = 0;
        int rowCount = 0;

//      生成标头
        Row row = sheet.createRow( rowCount++ );
        for(int i = 0 ;i < columnNameList.size() ; i++)
        {
            row.createCell(cellCount++).setCellValue(columnNameList.get(i));
            sheet.setColumnWidth(i,columnWidth);//设置列宽
        }
//      生成单元格内容
        for(int i = (page-1)*pageNumber ; i< contentList.size() && i < pageNumber*page ;i++ )
        {
            JSONObject jsonObject = contentList.get(i);
            row = sheet.createRow(rowCount++);
            for(int j = 0 ; j < columnNameList.size() ;j++){
                Cell cell = row.createCell(j);
                //cell.setCellStyle(cellStyleNewLine);
                cell.setCellValue(jsonObject.get(columnNameList.get(j)).toString());
            }
        }
    }
    
  //创建ROW 和CELL;传入的数据内容为数组集合
    @SuppressWarnings("unused")
	public static void createRowObject(Sheet sheet , Workbook wb ,int page)
    {
        CellStyle cellStyleNewLine = getCellStyleNewLine(wb);
        int cellCount = 0;
        int rowCount = 0;

//      生成标头
        Row row = sheet.createRow( rowCount++ );
        for(int i = 0 ;i < columnNameList.size() ; i++)
        {
            row.createCell(cellCount++).setCellValue(columnNameList.get(i));
            sheet.setColumnWidth(i,columnWidth);//设置列宽
        }
//      生成单元格内容
        for(int i = (page-1)*pageNumber ; i< contentListObject.size() && i < pageNumber*page ;i++ )
        {
            Object[] object = (Object[])contentListObject.get(i);
            row = sheet.createRow(rowCount++);
            for(int j = 0 ; j < columnNameList.size() ;j++){
                Cell cell = row.createCell(j);
                //cell.setCellStyle(cellStyleNewLine);
                cell.setCellValue(object[j]==null?"":object[j].toString());
            }
        }
    }
    
    /**
	 * excel表格下载
	 * @param response
	 * @param list ,表格内容为List<Object[]>
	 * 				Object[]数组内容顺序与表头相对应
	 * @param fileName 下载后Excel表格的文件名
	 * @param columnNameList 列名集合
	 * @throws IOException
	 */
	public static void exportExcel(HttpServletResponse response,List<Object[]> list ,List<String> columnNameList,String fileName) throws IOException{
		OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();

            response.setContentType("application/msexcel");
            response.setHeader("Content-Disposition", "attachment;filename=\"" + new String(fileName.getBytes(), "ISO8859-1") + "\"");
		    ExcelUtils.setSheetName(fileName.substring(0,fileName.indexOf(".")));//工作表名
	        ExcelUtils.setColumnNameList(columnNameList);//列名集合
	        ExcelUtils.setContentListObject(list);//列表内容
	        ExcelUtils.writeExcelObject(outputStream);
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
	}
	
	public static void main(String[] args){
		System.out.println(1%1000);
	}
}


